﻿-- Copyright 2007-2010 Comdiv (F. Sadykov) - http://code.google.com/u/fagim.sadykov/
-- Supported by Media Technology LTD 
--  
-- Licensed under the Apache License, Version 2.0 (the "License");
-- you may not use this file except in compliance with the License.
-- You may obtain a copy of the License at
--  
--      http://www.apache.org/licenses/LICENSE-2.0
--  
-- Unless required by applicable law or agreed to in writing, software
-- distributed under the License is distributed on an "AS IS" BASIS,
-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
-- See the License for the specific language governing permissions and
-- limitations under the License.
-- 
-- MODIFICATIONS HAVE BEEN MADE TO THIS FILE
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
---                      COMDIV MacroTSQL v2.0		
---						 CORE FUNCTIONALITY MODULE
---------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
--
--MODULTE INDEX
--
--1. Macro support for TSQL (called through DDL trigger during usual proc or func creation)
--	a) preprocessing of SQL on SP and F creation:
--		1)  comdiv_unified_procedure_and_function_creator - trigger, that reacts on CREATE PROC and CREATE FUNC if 
--			it's name is 'dbo._' ('_') - parses XML declaration and perform safe cleaning of real object, sql preparing
--			and real creation of needed procedure or function
--		2)  comdivprovate.preprocessor - dispatcher SP, used by trigger, find all SP in comdivpreprocessor schema 
--			and call them to rewrite sql in created proc
--		some preprocessors provided:
--		3)  comdivpreprocessor.x010_foreach_operator_support - supports macro-like '--foreach:' block - convert it
--			to safe cursor iteration over rowset (no any manual overhead with cursor mangment and fetching)
--		4)	comdivpreprocessor.x999_number_lines - executed at end of sql rewriting, suffixes all lines in code
--			with line number - very usefull when finding errors from messages and so on
--	b) aspect-oriented stored procedures support (Windsore facilities architecutre inspired)
--		aspects applyed if special attribute is seted on SP or F
--		1) comdivpreprocessor.x000_aspect_maker, comdivprivate.preprocess_insert_after, comdivprivate.preprocess_insert_before -
--			preprocessor for creation other preprocessors in 'aspect' style - aspects must provide 'before','after' or
--			both parts which are applyed to procedure body, wrap it, comdivpreprocessor.x000_aspect_maker provides context
--			for such procedures, so minimal code needed in aspects
--		some aspects provided by core:
--		2) comdivpreprocessor.x110_trace_aspect - insert message printing for start and finish of procedure (for debuging or script logging)
--		3) comdivpreprocessor.x100_transaction_aspect - wrap body of SP into "try tran [BODY] commit  catch.. rollback rethrow" logic
--		4) comdivpreprocessor.x100_failsafe_aspect - wrap body of SP into "try..catch" block and just prints error instead of reraise
--	c) attribute-like XML declaration support (C#, Boo inspired)
--		allow to create xml declaration in special comment block in SP declaration and use it as metadata of SP
--		1) comdiv.extractxmlcomment (_getcomment) - extracts xml declaration block from SQL definition
--		2) comdiv.extractxmlparam (_getparam) - reads value of named parameter (attribute) in declaration
--		3) comdiv.hasattribute (_hasparam) - checks existense of named parameter (attribute) in declaration
--2. Basic object-oriented support for table creation and upgrading:
--		0) you define 'interfaces' as SP which perform setting up of given table
--			interfaces can be depended (inheritance) from each other (implemented as calls from one interface
--			to another with same table), so it's not POSTGRES-like OOP support (tables have not real inheritance)
--			but you get centralized and well-syntaxed declaration support to express metadata templating,
--			interfaces itself are very close to real OOP (support of inheritanse, field and method declarations)
--		1) comdiv.addtable (_table), comdiv.definecolumn  - fail-safe SP to check and create if not exists of tables and columns on them
--			provides not usual for T-SQL batch syntax for table creation (not real SQL declaration) - usefull if we setting
--			up columns after table creation due to new tasks, comdiv.definecolumn used by 'interfaces' to express thier 'fields' definitions
--		2) schema comdiv_table_interface - namespace for SP which get @table as parameter and perform some processing for it
--		3) comdivpreprocessor.x300_table_interface_creator, aspect - react on procdures in comdiv_table_interface schema , 
--			generates additional 'before' aspect where provides interface inheritance and interface field embedding due
--			to special XML attributes 'import' - multiple and 'field' - multiple, so you can easly define 'field-only'
--			interfaces in pure declaration style (SP body can be used to create special table-oriented functions and so on) :
--				example of interface from our module for one project:

--				/*@ 

--				<!-- name mus be in comdiv_table_interface schema, name of interface is 'code' -->
--				<name>comdiv_table_interface.code</name>

--				<!-- here field for interface is defined -->
--				<field name='code' type='nvarchar(255)' constraint='not null unique default cast(newid() as nvarchar(255))' />
--
--				<decription>additional string pseudo primary key for repositories and user-friendly object naming and 
--					we not like using ids in code (work on different bases but code remains same)
--				</description>
--				@*/

--				create proc _ @table nvarchar(255), @useconstraints bit as begin 
					-- fields will be provided by declaration, here we define 'method' - which returns 'id' field by code
--					if object_id(@table+'_id') is null begin
--						declare @q nvarchar(max)
--						set @q = 'create function '+@table+'_id (@code nvarchar(255)) returns bigint as begin
--							return (select top 1 id from '+@table+' where code = @code )
--						end'
--						exec sp_executesql @q
--					end
--				end	
			
--
--		4) comdiv.implement - brings tablename and interface name and applys interface (from comdiv_table_interface schema) to
--			target table
--				after example before :
--					exec comdiv.implement 'mytable','code' will add 'code' field to 'mytable' , and function 'dbo.mytable_id(@code)' will be created
--
--3. Some utility stuff for simple DB object managment that used by core itself
--	a) comdiv.throw SP , comdiv.formaterror(...) F for simple error handling
--  b) comdiv.batch  SP - quick QUERY->SCRIPT TEXT generator for Dynamic SQL and generation tools
--  c) comdiv._drop  SP - "shugar" for DROP PROC, DROP FUNCTION ... - check existense and define type
--	d) comdiv.tostring(...)  F (_str) - "shugar" for converting sql_variant into nvarchar(max), nulls converted to ''
--  e) comdiv.defineschema SP - creates SCHEMAS safely
--	f) comdiv.definesynonym - creates or recreates synonyms safely

-- NOTES
--Module is 'ecologicaly clean' - 
--a) can be executed on any database
--b) can be executed many times
--c) does not affect anything in more complex context
--d) usual SP and F creation is not affected by module
--EXCEPTION:
--a)if you have an object that has name 'dbo._' module will not work


-- SCHEMAS:
-- 1. comdiv - 'public' SP and F without specialization (main schema)
-- 2. comdivprivate - 'private' SP and F that used internally by module and are not suggested to use directly
-- 3. comdivpreprocessor - special schema which is logical 'namespace' where MacroTSQL search procedurec to 
--		affect code on SP or F creation

-- 1. initialy we assume that 'comdiv' schema is existed on target database
if schema_id('comdiv') is null exec sp_executesql N'create schema comdiv' -- it's nice snippet!
go

-- 'ptivate' methods of module - not suggested to use it directly
if schema_id('comdivprivate') is null exec sp_executesql N'create schema comdivprivate' -- it's nice snippet!
go
---2 -- comdiv._drop -- procedure for dropping procedures and other objects by name 
--if they are exists ----(wanna postgres syntax!)
-- dropping of object is very usual operation and used almost anywhere in module
if object_id('comdiv._drop') is not null drop proc comdiv._drop
go
/*@
<name>comdiv._drop</name>
<module name="comdiv.mtsql.core" class="ddl" method="remove"/>
<description>
"shugar" procedure to drop procedures, functions, synonyms, views
1) checks object existense before try to drop
2) choose command by self, due to dropped object type
3) can use type constraint to constraint type for which given name is applyable
</description>
@*/
create proc comdiv._drop 
	@objectname nvarchar(255) = null, 
	@typeconstraint nvarchar(255) = null 
	as begin
	declare @query nvarchar(max)
	declare @message nvarchar(255)
	-- if nothing to drop simply return
	if isnull(@objectname,'')='' begin 
		print 'comdiv._drop was called without any name'
		return 0
	end
	declare @objid int set @objid = object_id(@objectname)
	--if still nothing to drop return
	if @objid is null begin
		return 0
	end
	declare @objtype nvarchar(255) set @objtype = (select type from sys.objects where object_id = @objid)
	if isnull(@typeconstraint,@objtype) != @objtype begin
		set @message = 'error : type of target named ' + @objectname + '( '+ @objtype +' )   in drop not mathch - (' + @typeconstraint + ')!'
		RAISERROR (@message,18,1)
	end
	
	if		@objtype = 'P'  begin 
		set @query = 'drop proc '+@objectname
	end
	else if @objtype = 'V'  begin 
		set @query = 'drop view '+@objectname
	end
	else if @objtype = 'FN' begin 
		set @query = 'drop function '+@objectname
	end
	else if @objtype = 'TF' begin 
		set @query = 'drop function '+@objectname
	end
	else if @objtype = 'SN' begin 
		set @query = 'drop synonym '+@objectname
	end
	else if @objtype = 'TR' begin 
		set @query = 'drop trigger '+@objectname
	end
	else begin
		-- such case is really error and exception - given name try to drop something else
		set @message = 'cannot drop object with name '+@objectname + ' because it''s type '+ @objtype +' is not supported'
		raiserror (@message,18,1)
	end
	set @message = 'executing : '+@query
	exec sp_executesql @query
	return 1
end
go
--frees special transient object _
comdiv._drop _
go
-- 3. -- comdiv.definesynonym -- creates or recreates synonym
comdiv._drop 'comdiv.definesynonym'
go
/*@
<name>comdiv.definesynonym</name>
<module name="comdiv.mtsql.core" class="ddl" method="definesynonym" />
<description>
creates or recreates synonym safely
</description>
@*/
create proc comdiv.definesynonym @name nvarchar(255), @target nvarchar(255) as begin 
	declare @message nvarchar(255)
	declare @query nvarchar(255)
	if isnull(@name,'') = '' or isnull (@target, '') = '' begin
		set @message = 'error : comdiv.definesynonym was called with empty parameters!'
		RAISERROR (@message,18,1)
	end
	exec comdiv._drop @name, 'SN'
	set @query = 'create synonym '+@name +' for '+@target
	set @message = 'executing: '+ @query
	exec sp_executesql @query
end
go
comdiv._drop 'comdiv.tostring'
go
/*@
<name>comdiv.tostring</name>
<module name="comdiv.mtsql.core" class="conv" method="tostring"/>
<synonym>_tostring</synonym>
<description>converts any value to nvarchar(max)</description>
@*/
create function comdiv.tostring (@v sql_variant) returns nvarchar(max) as begin
	return isnull(cast(@v as nvarchar(max)),'')
end
go
 comdiv._drop 'comdiv.getdeclaration'
go

/*@
<name>comdiv.getdeclaration</name>
<module name="comdiv.mtsql.core" class="reflection" method="getdeclaration"/>
<description>
it's good idea to use declarative syntax and attributes to impress some metadata
about language objects. Python,Boo,C#.. many of languages allow attributes to
be not only documentation, but rulers of object behaviour in compilation, reflection
or in another context.
Another good thing is inline XML documentation (C#).
Here, in this Core module we try to made such abilities in T-SQL.
Design:
	before object definition developer places DECLARATION BLOCK
	DECLARATION BLOCK is MULTILINE COMMENT with additional @ symbol after opener and before closer (as you can see here)
	BODY of DECLARATION BLOCK is WELLFORMED XML FRAGMENT (no need to define root element)
	BODY consists with :
		1) value attributes - XML elements with text value
		2) flag attributes - empty XML elements
		3) special attributes - any valid XML for custom processing
	ANY ATTRIBUTE MUST appear once in set
	name of element is name of attribute (parameter)
	DECLARATION BODY can be exracted by this (comdiv.extractxmlcomment (_getcomment)) function
	VALUE OF VALUED ATTRIBUTE can be extracted by comdiv.extractxmlparam (_getparam) function
	EXISTENCE OF FLAG ATTRIBUTE can be checked by comdiv.hasxlparameter (_hasparam) function
NOTES:
	function returns '<empty/>' xml if no declaration exists in given code (not NULL)
</description>
@*/
create function comdiv.getdeclaration (@str nvarchar(max)) returns xml as begin
	declare @result xml
	declare @start int, @end int, @txt nvarchar(max)
	set @start = charindex('/*@', @str, 0)
	set @end = charindex('@*/', @str, 0)
	if ( @start + @end ) = 0 return '<empty/>'
	set @txt = substring(@str, @start+3, @end - @start - 3)
	
	set @result = @txt
	return @result
	if @@error != 0
		set @result = '<error><text><![CDATA['+@str+']]></text><message><![CDATA['+comdiv.formaterror(default)+']]></message></error>'
	return @result
	
end
go
 comdiv._drop 'comdiv.getattribute'
go

/*@
<name>comdiv.getattribute</name>
<module name="comdiv.mtsql.core" class="reflection" method="getattribute"/>
<description>
Returns value of VALUED XML PARAMETER (see comdiv.extractxmlcomment for details)
</description>
@*/
create function comdiv.getattribute (@str nvarchar(max), @param nvarchar(255)) returns nvarchar(max)  as begin
	declare @result nvarchar(max)
	set @result = comdiv.getdeclaration(@str).value('(//*[local-name()=sql:variable("@param")])[1]','nvarchar(max)')
	set @result = ltrim(rtrim(@result))
	if ascii(@result)=10  set @result = right(@result,len(@result)-1)
	if ascii(right(@result,1))=10 set @result = left(@result,len(@result)-1)
	set @result = ltrim(rtrim(@result))
	return @result
end
go
 comdiv._drop 'comdiv.getsourcesql'
go
/*@
<name>comdiv.getsourcesql</name>
<module name="comdiv.mtsql.core" class="reflection" method="getsourcesql"/>
<description>
retrieves source sql script from special comment in tail of script
</description>
@*/
create function comdiv.getsourcesql (@str nvarchar(max)) returns nvarchar(max)  as begin
	declare @result nvarchar(max)
	declare @idx int set @idx = charindex('/*####',@str,0)
	if(@idx = 0)return ''
	set @result = right(@str, len(@str)-@idx+1)
	set @result = replace(@str,'/*####','')
	set @result = replace(@str,'####*/','')
	set @result = replace(@str,'~*','/*')
	set @result = replace(@str,'*~','*/')
	set @result = rtrim(ltrim(@result))
	return @result
end
go

comdiv._drop 'comdiv.getsubattribute'
go

/*@
<name>comdiv.getsubattribute</name>
<module name="comdiv.mtsql.core" class="reflection" method="getsubattribute"/>
<description>
Returns value of attribute nested into element of declaration
</description>
@*/
create function comdiv.getsubattribute (@str nvarchar(max), @element nvarchar(255), @param nvarchar(255)) returns nvarchar(max)  as begin
	declare @result nvarchar(max)
	set @result = comdiv.getdeclaration(@str).value('(//*[local-name()=sql:variable("@element")]/@*[local-name()=sql:variable("@param")])[1]','nvarchar(max)')
	set @result = ltrim(rtrim(@result))
	if ascii(@result)=10  set @result = right(@result,len(@result)-1)
	if ascii(right(@result,1))=10 set @result = left(@result,len(@result)-1)
	set @result = ltrim(rtrim(@result))
	return @result
end
go
comdiv._drop 'comdiv.hasattribute'
go
/*@
<name>comdiv.hasattribute</name>
<module name="comdiv.mtsql.core" class="reflection" method="hasattribute"/>
<description>
Returns 1 if given FLAG XML PARAMETER exists in declaration (see comdiv.extractxmlcomment for details)
</description>
@*/
create function comdiv.hasattribute (@str nvarchar(max), @param nvarchar(255))returns bit  as begin
	declare @paramx xml
	set @paramx = comdiv.getdeclaration(@str).query('//*[local-name()=sql:variable("@param")]')
	if isnull(cast(@paramx as nvarchar(max)),'') = '' return 0
	return 1
end
go
comdiv._drop 'comdivprivate.preprocesssql'
go
-- here it's just created and reserved because it's used in trigger, defined below
create proc comdivprivate.preprocesssql /*pre-version*/ @e xml, @sql nvarchar(max) output as begin
	return 0 -- initially null function - stub
end
go

if (select object_id from sys.triggers where name = 'comdiv_unified_procedure_and_function_creator') is not null drop trigger comdiv_unified_procedure_and_function_creator on database
go
/*@
<name>dbo.comdiv_unified_procedure_and_function_creator</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="entrypoint"/>
<description>
	DDL trigger for procedure and function creation

it's very powerfull trigger that is real solution for name synchronization for DROP IF EXISTS AND THEN CREATE logic
and main approach is REAL (not as custom tool) EMBEDDING of ATTRIBUTE-based, ASPECT-oriented and preprocessable support
to T-SQL

	provides following abilities:
1) safe cleaning of existed objects 
	(so you always make calls to create command, no needs decide - CREATE or ALTER you need,
		this behavoiur impressed by POSTGRES 'create or replace function' construction)
2) migration of object names (in incremental schema updates)
3) preprocessing of SQL code of functions and procedures - ability for
	implicite aspect programming and implicite templating in T-SQL
4) automatic SYNONYM creation ability

REQUIREMENTS
1) not ALL procedures and functions are affected by trigger - usually defined SP and F are ignored
2) procedure or function MUST have name == '_' (create proc _ ..., create function _) - this is :
	a) marker to delimit 'advanced comdiv procedures'
	b) allow avoid name collisions in creation if procedure already exists
	c) '_' name is special name which lives temporary and safely drops by trigger after real procedure
	with real name is created	
	so you can see that many procedures in core module and ALL procedures and functions in 
	depended modules have "name" '_'
3) procedure or function MUST have XML ATTRIBUTE DEFINITION BLOCK with at least 'name' attribute

LIMITATIONS
1) it cannot be used for functions that are used in VIEWS or in computed COLUMNS of TABLES
because trigger drops objects before creation
2) stored procedures ALWAYS MUST HAVE MAIN begin..end block (no default body) because it used to 
find start and end of body to embed aspects
3) by design trigger is executed BEFORE COMMITING of procedure creation, but not BEFORE creation
itself, so, while SQL will be preprocessed it STILL must be computable valid script 

HOW IT WORKS
1) if procedure is '_'-named it bring it to work
2) if not XML Attributes provided, or name is not provided - error occured and creation rollbacked
3) '_' procedure (or function) dropped (it was needed only to get SQL)
4) real name is geted from 'name' XML Attribute
5) object with real name is dropped (to allow creation of new object)
6) if 'synonym' XML Attribute provided - synonym created with it's value as name and 'real name' as target
7) if 'oldname' and/or 'oldsynonym' attributes provided - additional comdiv._drop applyed to cleanup objects 
with obsolete names
8) '_' name in SQL script replaced with real name
9) SQL of source procedure or function is processed by COMDIV PREPROCESSOR (see comdivprivate.preprocessor)
10) if 'showsqloncreate' XML Attribute is setted on created procedure - SQL is printed (for debugging)
11) processed script is executed
AS RESULT
1) '_' name never persists in DATABASE and always available for new calls,
2) all objects cleaned and recreated
3) SQL script is fully preprocessed	
</description>
@*/
create trigger comdiv_unified_procedure_and_function_creator on database
for create_procedure, create_function, create_view,create_trigger
as begin
	set nocount on 
	declare @ed xml set @ed = eventdata()
	declare @x xml 
	declare @schema nvarchar(255) set @schema = @ed.value('(//SchemaName)[1]','nvarchar(255)')
	declare @name nvarchar(255)set @name = @ed.value('(//ObjectName)[1]','nvarchar(255)')
	declare @sql nvarchar(max)set @sql = @ed.value('(//CommandText)[1]','nvarchar(max)')
	declare @syn nvarchar(255)
	declare @oldname nvarchar(255)
	declare @oldsql nvarchar(max)
	declare @oldsyn nvarchar(255)
	declare @isview bit set @isview = 0
	declare @sqlstartindex int
	if (@ed.value('(//ObjectType)[1]','nvarchar(255)')) = 'VIEW' set @isview = 1
	declare @istrigger bit set @istrigger = 0
	if (@ed.value('(//ObjectType)[1]','nvarchar(255)')) = 'TRIGGER' set @istrigger = 1
	declare @showsql bit
	declare @q nvarchar(max)
	declare @istemp bit set @istemp = 0
		if (@schema = 'dbo' or @istrigger=1) and @name = '_' begin
			set @x = comdiv.getdeclaration(@sql)
			set @name = comdiv.getattribute(@sql,'name')
			if isnull(@name,'') = '' begin
				if (@ed.value('(//ObjectType)[1]','nvarchar(255)')) = 'PROCEDURE' begin
					set @istemp = 1
					set @name = '__temporary_macro_tsql_procedure'
					set @x = @x.query('for $n in //* return $n,<name>__temporary_macro_tsql_procedure</name>')
				end else begin
					print 'can define temporary procedure only'
					rollback
					return
				end
			end			
			set @sqlstartindex = charindex('@*/',@sql,0)
		
			if(@sqlstartindex = 0) begin
				set @oldsql = @sql
			end
			else begin 
				set @oldsql = right(@sql,len(@sql)-@sqlstartindex -3)
				set @oldsql = replace(@oldsql,'/*','~*')
				set @oldsql = replace(@oldsql,'*/','*~')
			end
			
			if @istemp = 1
				set @sql ='/*@'+cast(@x as nvarchar(max))+'@*/'+@oldsql

			set @syn = comdiv.getattribute(@sql,'synonym')
			set @oldname = comdiv.getattribute(@sql,'oldname')
			set @oldsyn = comdiv.getattribute(@sql,'oldsynonym')
			set @showsql = comdiv.hasattribute(@sql,'showsqloncreate')
			if @showsql = 0 set @showsql = comdiv.hasattribute(@sql,'sql')
			
			if isnull(@oldname,'') != '' begin
				exec comdiv._drop @oldname
			end
			if isnull (@oldsyn,'') != '' begin
				exec comdiv._drop @oldsyn
			end
			exec comdiv._drop @name
			if isnull(@syn,'') != '' begin
				exec comdiv.definesynonym @syn, @name
			end 
			declare @cleanup nvarchar(255) set @cleanup = '_'
			if @istrigger = 1 set @cleanup = @schema +'._'
			exec comdiv._drop @cleanup	
			declare @begin nvarchar(max)
			declare @body nvarchar(max)
			declare @_end_of_decl nvarchar(255) set @_end_of_decl = ' begin'
			if @isview = 1 set @_end_of_decl = 'select'
			declare @idx int set @idx = charindex(@_end_of_decl,@sql,charindex(' _ ', @sql, charindex('create',@sql,0)))
			if @idx <= 0 print @sql
			set @begin = left(@sql,@idx-1)
			set @body = right(@sql,len(@sql) - len(@begin))

			set @begin = replace(@begin, ' _ ', ' ' + @name + ' ' )
			set @sql = @begin + @body
			
			exec comdivprivate.preprocesssql  @ed, @sql output
			if comdiv.hasattribute(@sql,'nosrcsql') = 0 begin
			set @sql  = @sql + '


				/*####
			'+@oldsql+'
				####*/
			'
			end
			if @showsql = 1 print @sql
			exec sp_executesql @sql	
	end
end
go

if (select object_id from sys.triggers where name = 'comdiv_mtsql_temporary_procedure_executor') is not null drop trigger comdiv_mtsql_temporary_procedure_executor on database
go
/*@
<name>dbo.comdiv_mtsql_temporary_procedure_executor</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="entrypoint"/>
@*/
create trigger comdiv_mtsql_temporary_procedure_executor on database
after create_procedure
as begin
	set nocount on 
	declare @ed xml set @ed = eventdata()
	declare @name nvarchar(255)set @name = @ed.value('(//ObjectName)[1]','nvarchar(255)')
	if @name = '__temporary_macro_tsql_procedure' begin
		exec __temporary_macro_tsql_procedure
		drop procedure __temporary_macro_tsql_procedure
	end	
end
go


/*@
<name>comdiv.defineschema</name>
<_sql/>
<module name="comdiv.mtsql.core" class="ddl" method="defineschema"/>
<synonym>_schema</synonym>
<description>
	safe method for creation of SCHEMAS - check existense before trying of creation
</description>
@*/
create proc _  @schemaname nvarchar(255) as begin
	declare @message nvarchar(255)
	declare @query nvarchar(255)
	
	if isnull(@schemaname,'')='' begin 
		print 'comdiv.assumeschema was called without any name'
		return 0
	end

	if schema_id(@schemaname) is null begin
		set @query = 'create schema '+@schemaname
		set @message = 'executing: '+@query
		exec sp_executesql @query
	end
	
end
go
-- special schema wich locates procedures to be called from comdivprivate.preprocesssql
comdiv.defineschema 'comdivpreprocessor'
go
-- schema for defining 'table interfaces'
comdiv.defineschema 'comdiv_table_interface'
go
/*@
<name>comdiv.formaterror</name>
<module name="comdiv.mtsql.core" class="errors" method="format"/>
<description>
	Converts existed system error to readable string with all parameters of error
	uses @module parameter for more qualified mapping to place of error occuranse
	(if stored proc level is not match needs)
</description>
@*/
create function _ (@module nvarchar(255) = null)  returns nvarchar(max) as begin 
	declare @result nvarchar(max)
	set @module = isnull(@module,'')
	IF ERROR_NUMBER() IS NOT NULL -- системная ошибка
	BEGIN
	    	DECLARE 
			@SysErrorMessage    NVARCHAR(4000),
			@errorNumber     NVARCHAR(200),
			@errorSeverity   NVARCHAR(200),
			@errorState      NVARCHAR(200),
			@errorLine       NVARCHAR(200),
			@errorProcedure  NVARCHAR(200)

		SELECT 
			@ErrorNumber = ERROR_NUMBER(),
			@ErrorSeverity = ERROR_SEVERITY(),
			@ErrorState = ERROR_STATE(),
			@ErrorLine = ERROR_LINE(),
			@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '<unknown>'),
			@SysErrorMessage = ISNULL(ERROR_MESSAGE(), 'no message');
		-- Compose an error message with all of the details we have.
		set @result = comdiv.tostring(@errorseverity) + ' :: '+ @errorstate + ' --> error :
module: ' + @module +' 
procedure: ' +  @errorprocedure + ' 
line :' + @errorline + ' 
message :' + @syserrormessage
	END ELSE BEGIN -- пользовательская ошибка
		-- No extra error information!
		-- Raise the error anyways using what we know.
		set @result = 'unknown error (' + @module + ')'
	END
	return @result
end
go
/*@
<name>comdiv.printerror</name>
<module name="comdiv.mtsql.core" class="errors" method="print"/>
<description>
	simply prints error to console - for silent catches
</description>
@*/
create proc _ (@module nvarchar(255) = null) as begin 
	declare @message nvarchar(max) set @message = comdiv.formaterror(@module)
	print @message
end
go
/*@
<name>comdiv.throw</name>
<module name="comdiv.mtsql.core" class="errors" method="throw"/>
<description>
<_sql/>
provides procedure which wraps RAISERROR with following features:
1) messages and other parts of ERROR is constructed with usage of both user and system error context,
so if throw is called from CATCH block, existed error will be propagated
2) more detailed error info provided (based on comdiv.formaterror function)
Parameters :
	@modulename - use to define place where error occured (if stopred procedure level is to small or to big to describe)
	@severity - uses if it's not sys error propagation, only for user's errors
	@errormsg - advanced error message used with system error message
</description>
@*/
create proc _  @moduleName NVARCHAR(255), @severity int, @errorMsg nvarchar(max) AS
	SET NOCOUNT ON
	SET LOCK_TIMEOUT 30000
	
	IF ERROR_NUMBER() IS NOT NULL -- системная ошибка
	BEGIN
		declare @message nvarchar(max) set @message = comdiv.formaterror(@moduleName)
		declare @errorseverity nvarchar(200) set @errorseverity = ERROR_SEVERITY()
		declare @ErrorState nvarchar(200) set @ErrorState = ERROR_STATE()
		RAISERROR(@message, @ErrorSeverity, @ErrorState)
	END ELSE BEGIN -- пользовательская ошибка
		-- No extra error information!
		-- Raise the error anyways using what we know.
		RAISERROR('%s :: %s', @severity, 1, @ModuleName, @ErrorMsg)
	END
GO
comdiv._drop 'comdiv.batch'
go
/*@
<name>comdiv.batch</name>
<module name="comdiv.mtsql.core" class="utils" method="batch"/>
<description>
comdiv.batch converts result of select into text with formatting tablerow==>line in text,
in additional it can emmidiatly execute it as script
How it work:
1) construct (or uses) select instruction to create table
2) uses XML facilities to create ordered concatenation from table expressed as "select * for xml auto"
3) normalizes XML entities and newline symbols
4) if choosed - executes result as SQL script
5) returns nor
parameters:
	@columnnames - list of columns (only names, comma delimited) 
				which must be match names in SELECT and used as attributes names in XML,
				all columns will be used as nvarchar(max), so all of selected columns must support implicite conversion
				to string or have to be casted
	@dataquery - fully qualified SELECT instruction with columns that are match @columnnames list,
				or it can be part of SELECT AFTER 'FROM' keyword - in this case batch will construct
				start of select itself, using @columnnames (short syntax usefull for simple one-table queries)
				to simplify writing and reading calls to batch it converts all double (") quotes into single (')
				but it means that you MUST use [...] syntax for object names instead of "..." syntax, and
				problems will be with literals that contains '"' - avoid them by using "&quot;" which will be
				converted to (") on SQL side and will be parsed again from XML
	@format - string which express format of every row in result batch
				will be embeded in XQuery 'concat' function as parameter list, so it MUST match it's syntax
				(remember you can use usual quotes in it)
				row supplyed to format as $t variable, so if you want to access 'name' column, use $t/@name construction
	@order  - single XML order clause, row provided as $t, direction - ascending, optional
	@execute - 1 - to execute result batch as script, 0 - skip execution, 0 is default
	@result output - parameter to return result script to caller


SEE EXAMPLES OF USAGE IN COMDIV CORE, COMDIV UTILS MODULES in 
comdivprivate.preprocesssql 

It's one of substitution for cursor, another one is --foreach: block support

</description>
@*/
create proc comdiv.batch @columnnames nvarchar(max),  @dataquery nvarchar(max),  @format nvarchar(max), @order nvarchar(255) = null, @execute bit = 0, @result nvarchar(max) = null output  as begin
	set nocount on 
	declare @query nvarchar(max)
	declare @xquery nvarchar(max)
	declare @ret nvarchar(2) set @ret = char(13)
	declare @x xml
	create table #t(stub int)
	create table #x1 (x xml)
	create table #x2 (x xml)
	set @query = 'alter table #t add '+ (replace(@columnnames,',',' nvarchar(max),') + ' nvarchar(max) ')
	exec sp_executesql @query
	alter table #t drop column stub
	if @dataquery not like 'select%' set @dataquery = 'select '+ @columnnames + ' from '  + @dataquery
	set @dataquery = replace(@dataquery,'"','''')
	set @dataquery = replace(@dataquery,'&quot;','"') 
	--print @dataquery
	
	set @query = 'insert #t select * from ('+ @dataquery + ') as t'	
	--print @query
	exec sp_executesql @query
	set @x = (select * from #t as t for xml auto)

	insert #x1 (x) values (@x)
	set @xquery = 'for $t in //t ' 
	if isnull(@order,'')!='' set @xquery = @xquery + ' order by '+ @order
	set @xquery =  @xquery + ' return concat(' + @format + ',"_RETURN_")'
	set @xquery = replace(@xquery, '''', '''''')
	
	set @query = '
		declare @x xml
		set @x = (select top 1 x from #x1)
		set @x = @x.query(''' + @xquery + ''')'+'
		insert #x2 (x) values (@x)
	'
	exec sp_executesql @query
	
	set @result =  (select top 1 cast(x as nvarchar(max)) from #x2) 
	set @result =replace(@result,'_RETURN_',@ret)
	set @result = replace(@result ,'&lt;' ,'<') 
	set @result = replace(@result ,'&gt;' ,'>')
	set @result = replace(@result ,'&quot;' ,'"')
	--print @result

	if @execute = 1 begin
		exec sp_executesql @result
	end
end
go
comdiv._drop  'comdivprivate.preprocessrunner'
go
/*@
<name>comdivprivate.preprocessrunner</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="steprunner"/>
<description>
</description>
@*/
create proc comdivprivate.preprocessrunner /*cannot use commented style because it's used in trigger */
	@procname nvarchar(255) as begin
	begin try
		set @procname = 'comdivpreprocessor.'+@procname
		declare @q nvarchar(max) set @q = 'exec ' + @procname
		declare @sql nvarchar(max) set @sql = (select top 1 s from #preprocess)
		if comdiv.hasattribute(@sql,'debug')=1 begin
			print '---- enter ----'
			print @procname
			print '--------------
				---SQL-----'
			print @sql
			print '-----------------------------------
-------------------------------------------------------'
		end 

		exec sp_executesql @q
		
		if comdiv.hasattribute(@sql,'debug')=1 begin
			set @sql = (select top 1 s from #preprocess)
			print '---- result of ----'
			print @procname
			print '--------------
				---SQL-----'
			print @sql
			print '--------------------------------------------
------------------------------------------'
		end
	end try
	begin catch
		exec comdiv.throw @procname,18,'in preprocessor'
	end catch
end
go
comdiv._drop  'comdivprivate.preprocesssql'
go
/*@
<name>comdivprivate.preprocesssql</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="dispatcher"/>
<description>
dispatcher procedure which performs advanced processing
of SQL privided by CREATE PROC or CREATE FUNCTION command
Called from DDL trigger comdiv_unified_procedure_and_function_creator

0) don't contains self logic
1) creates #preprocess table and stores initial SQL script here
2) creates #eventdata table and stores DDL event XML here
3) finds all procedures that has shema 'comdivpreprocessor'
     a) procedures MUST not have non-optional parameters
	 b) order of calling must be seted by proc name (will be ordered)
	 c) procedures MAY change value of #preprocess table as their's work
4) make calls to all of finded procedures, ordered by name 
	(so order can be impressed in numbered name prefix)
5) returns current value of row in #preprocess
</description>
<event>comdivpreprocessor</event>
@*/
create proc comdivprivate.preprocesssql /*cannot use commented style because it's used in trigger */
	 @e xml, @sql nvarchar(max) output as begin
	begin try
		declare @script nvarchar(max)
		create table #preprocess (s nvarchar(max))
		create table #eventdata (x xml)
		insert #preprocess (s) values (@sql)
		insert #eventdata (x) values (@e)
		exec comdiv.batch 'name', 'sys.objects where type="P" and schema_id = schema_id("comdivpreprocessor")',
			'" exec comdivprivate.preprocessrunner  @procname=",$t/@name','$t/@name ',0, @script output
		exec sp_executesql @script
		set @sql = (select top 1 s from #preprocess)
		set @sql = REPLACE(@sql,'"MTSQLSTART"','ZZZZZ')
		set @sql = REPLACE(@sql,'"MTSQLENDPROC"','XXXXX')
		set @sql = REPLACE(@sql,'MTSQLSTART','/*@')
		set @sql = REPLACE(@sql,'MTSQLENDPROC','@*/ create proc _ as begin return end')
		set @sql = REPLACE(@sql,'ZZZZZ','"MTSQLSTART"')
		set @sql = REPLACE(@sql,'XXXXX','"MTSQLENDPROC"')
	end try
	begin catch
		declare @message nvarchar(max) set @message = 'ERROR OCCURED WHILE PREPROCESSING:
-------------------------------------------------------------------------------------

 ' + cast(@e as nvarchar(max)) + '
-------------------------------------------------------------------------------------
'+
comdiv.formaterror('comdivprivate.preprocesssql')	
		print @message
	end catch
end
go
comdiv._drop 'comdivprivate.preprocess_insert_before'
go
/*@
<name>comdivprivate.preprocess_insert_before</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="__insert_txt_before_body"/>
<description>
finds 'as begin' point in procedure or function 
and insert given content emmidiatly after  it and 1 line
used in aspect_maker
not significant needs to use it directly
most aspects uses 'aspect' attribute and preprocessor uses
insertion implicitely
</description>
@*/

create proc comdivprivate.preprocess_insert_before @sql nvarchar(max) output, @txt nvarchar(max) as begin
	set @sql = ltrim(rtrim(@sql))
	declare @idx int 
	
	declare @pattern nvarchar(255)
	set @pattern = '%as%begin%'
	set @idx = charindex('@*/',@sql) -- find end of 'attributes' construction
	if @idx != 0 set @pattern = '%@*/%as%begin%'

	set @idx = patindex(@pattern,@sql)
	set @idx = charindex('begin',@sql,@idx)
	set @sql = left(@sql,@idx+5)+'
'+@txt+'
'+right(@sql,len(@sql)-@idx-5)
end
go
comdiv._drop 'comdivprivate.preprocess_insert_after'
go
/*@
<name>comdivprivate.preprocess_insert_after</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="__insert_txt_after_body"/>
<description>
finds last 'end' operator of procedure or function 
and insert given content emmidiatly before it and 1 symbol else
used in aspect_maker
not significant needs to use it directly
most aspects uses 'aspect' attribute and preprocessor uses
insertion implicitely
</description>
@*/
create proc comdivprivate.preprocess_insert_after @sql nvarchar(max) output, @txt nvarchar(max) as begin
	declare @res nvarchar(max) set @res = @sql
	set @res = reverse(@res)
	declare @idx int set @idx = charindex('dne',@res,0)
	set @res = right(@res, len(@res) - @idx - 3)
	set @res = reverse(@res)
	set @sql = @res + @txt + '
 end
'
end
go
/*@
<name>comdivpreprocessor.x900_expand_dynamic_query</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="expand_dynamic_sql"/>
<nolinenumbers/>
<nodynamic/>
<nosplices/>
<handler/>
<description>

SP dynamic query expander - allow to use this syntax:
exec _ '&lt;&lt;&lt;create table &lt;&lt;@name&gt;&gt; (id int)&gt;&gt;&gt;' <!-- <<<create table <<@name>> (id int)>>> -->
instead of
declare @q nvarchar(255)
set @q = 'create table '+@name+' (id int)'
exec sp_executesql @q
&lt;&lt;&lt; and &gt;&gt;&gt; are needed to easy define start and end of dynamyc block
exec _ is needed
use &lt;&lt;...&gt;&gt; instead of string concatenation
</description>
@*/ 
create proc _  @test nvarchar(max) = null as begin
	declare @sql nvarchar(max)
	if @test is not null	
		set @sql = @test	
	else 
		 set @sql =  (select top 1 s from #preprocess)
	if @test is not null print @sql
	if not comdiv.hasattribute(@sql,'nodynamic')=1   begin
		if  @sql like '%<<<%' begin
		exec comdivprivate.preprocess_insert_before @sql output,'
			declare @____dynamic_sql_query nvarchar(max)
		'
		end
		set @sql = replace(@sql,'>>>''','''
			exec sp_executesql @____dynamic_sql_query
		')
		set @sql = replace(@sql,'''<<<','''')
		
		set @sql = replace(@sql,'exec _ ','set @____dynamic_sql_query =')
		
		
		update #preprocess set s = @sql
	end
end
go
/*@
<name>comdivpreprocessor.x910_expand_splices</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="expand_splices"/>
<nolinenumbers/>
<nodynamic/>
<nosplices/>
<handler/>
<description>

</description>
@*/ 
create proc _  @test nvarchar(max) = null as begin
	declare @sql nvarchar(max)
	if @test is not null	
		set @sql = @test	
	else 
		 set @sql =  (select top 1 s from #preprocess)
	if @test is not null print @sql
	if not comdiv.hasattribute(@sql,'nosplices')=1   begin
		set @sql = replace(@sql,'<<',''' + cast(')
		set @sql = replace(@sql,'>>',' as nvarchar(max)) + ''')
		set @sql = replace(@sql,'{{''','{{')
		set @sql = replace(@sql,'''}}','}}')
		set @sql = replace(@sql,'{{',''' + cast(')
		set @sql = replace(@sql,'}}',' as nvarchar(max)) + ''')	
		set @sql = replace(@sql,'""','''')	
		update #preprocess set s = @sql
	end
end
go

/*@
<name>comdivpreprocessor.x000_aspect_maker</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="process_if_is_aspect"/>
<nolinenumbers />
<description>
<handler/>
SP 'ASPECT' ASPECT
wrapper for other usual aspects which inserts something before and after 
procedure or function body
aspect_maker prepare well-formed context for making aspects easly
activated by 'aspect' attribute
if applyed:
0) no significant need directly work with #preprocess table and @e and @sql variables
1) main aspect body can contains calls to:
	a)insert #before(s) values ('...') to insert something before target procedure body
and
	b)insert #after(s) values ('...') to insert something after target procedure body
2) @sql and @name variables can be accessed with pseudo-strings '$sql' and '$name' anywhere in code:
		set @myvar = comdiv.getdeclaration('$sql')
3) to directly embed target procedure name in string use ##name construction: 
		print 'this is message from ##name '
4) for more well reading you can embed target procedure name 'as parameter' in sql generation with #name construction:
		exec comdiv.throw #name, 18, ''transactional error'' - if it's in 'after' or 'before' sql string - aspect
		maker will convert it to
		exec comdiv.throw'+ @name+ ', 18, ''transactional error'' 
LOOK source code for other aspect's in this module to see how it work
</description>
@*/
create proc _  as begin
	declare @sql nvarchar(max) set @sql = (select top 1 s from #preprocess)
	declare @name nvarchar(max) set @name = comdiv.getattribute (@sql,'name')
	if comdiv.hasattribute(@sql,'aspect')=1 begin
		exec comdivprivate.preprocess_insert_before @sql output,
		'
		declare @message nvarchar(max) 
		declare @sql nvarchar(max) set @sql = (select top 1 s from #preprocess)
	declare @name nvarchar(max) set @name = comdiv.getattribute (@sql,''name'')
		declare @before nvarchar(max)
		declare @after nvarchar(max)
		create table #sql (s nvarchar(max)) insert #sql (s) values (@sql)
		create table #before (s nvarchar(max)) 
		create table #after (s nvarchar(max)) 
		'
	declare @q nvarchar(max) set @q = '
		begin try
			set @before = (select top 1 s from #before )
			if isnull(@before,'''') != '''' begin
				--print ''dobefore''
				exec comdivprivate.preprocess_insert_before @sql output,@before
			end
		end try
		begin catch
			set @message = comdiv.formaterror(default)
			print @message
		end catch
		begin try
			set @after = (select top 1 s from #after )
			if isnull(@after,'''') != ''''
				exec comdivprivate.preprocess_insert_after @sql output,@after
			
		end try
		begin catch
			set @message = comdiv.formaterror(default)
			print @message
		end catch
	 update #preprocess set s = @sql
	--select * from #before
	--select * from #after
	
	--print @sql
'	
		set @sql = replace(@sql, '''''$sql''''','@sql')
		set @sql = replace(@sql, '''''$name''''','@name')
		set @sql = replace(@sql, '''$sql''','@sql')
		set @sql = replace(@sql, '''$name''','@name')
		set @sql = replace(@sql, '##name','''  + @name  + ''')
		set @sql = replace(@sql, '#name',''' + '''''''' + @name + '''''''' + ''')
		exec comdivprivate.preprocess_insert_after @sql output,@q

		update #preprocess set s = @sql
	end
end


go
/*@
<name>comdivpreprocessor.x100_transaction_aspect</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="wrapintotransaction"/>
<aspect />
<handler/>
<nolinenumbers />
<description>
SP TRANSACTION ASPECT
wrap code in try..catch block and
'begin tran/commit/rollback on error' logic
if error occured, transaction is rollbacked
and error propagated by call to throw
activated by 'transaction' attribute
error propagation can be cancelled by
'norethrow' attribute, printing message
will be used instead
</description>
@*/ 
create proc _  as begin
	if comdiv.hasattribute('$sql','transaction')=1 begin
	insert #before(s) values ('
		begin try
		begin tran	
		')
	if comdiv.hasattribute('$sql','norethrow')=1 
	insert #after(s) values ( '
	 commit
	end try
	begin catch
		if @@trancount > 0 rollback
		print '' ##name ROLLBACKED !!! ''
		declare @___errormessage nvarchar(max) 
			set @___errormessage = comdiv.formaterror(''##name'')
		print @___errormessage
	end catch
')		
	else
	insert #after(s) values ( '
	 commit
	end try
	begin catch
		if @@trancount > 0 rollback
		print '' ##name ROLLBACKED !!! ''
		exec comdiv.throw #name, 18, ''transactional error''
	end catch
')	
	end
end
go

/*@
<name>comdivpreprocessor.x100_failsafe_aspect</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="wrapinfailsafeblock"/>
<aspect />
<handler/>
<nolinenumbers />
<description>
SP FAILSAFE ASPECT
wrap code in try..catch block and
just prints erorr instead of reraising it
</description>
@*/ 
create proc _  as begin
	if comdiv.hasattribute('$sql','failsafe')=1 begin
	insert #before(s) values ('
		begin try
		')
	insert #after(s) values ( '
	 end try
	begin catch
		declare @___errormessage nvarchar(max) 
			set @___errormessage = comdiv.formaterror(''##name'')
		print @___errormessage
	end catch
')		
	end
end
go

/*@
<name>comdivpreprocessor.x110_trace_aspect</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="addtracepoints"/>
<aspect />
<handler/>
<nolinenumbers />
<description>
SP TRACE ASPECT
inserts print calls on start and end of procedure call
activated by 'trace' attribute
</description>
@*/ 
create proc _  as begin
	if comdiv.hasattribute('$sql','trace')=1 begin
		insert #before(s) values ('
			print '' procedure "{{@name}}"	started ''
			')
		insert #after(s) values ( '
		 print ''procedure "{{@name}}" finished ''
	')		
	end
end
go
/*@
<name>comdivpreprocessor.x999_number_lines</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="writelinenumbers"/>
<description>
<nolinenumbers/>
<handler/>
SP NUMBER LINES PREPROCESSOR
inserts line number at end of any line of SP or FUNC code
activated by default
can be deactivated (mostly on other preprocessors) by 'nolinenumbers' attribute
</description>
@*/ 

create proc _  @test nvarchar(max) = null as begin
	declare @sql nvarchar(max) 
	if @test is null set @sql =  (select top 1 s from #preprocess)
	if @test is not null set @sql = @test
	if comdiv.hasattribute(@sql,'nolinenumbers')= 0 begin
		if @test is not null print @sql
		declare @idx int set @idx = 0
		declare @idx2 int
		declare @line int set @line = 0
		set @sql = replace(@sql,char(10)+char(13),char(10))
		set @sql = replace(@sql,char(13)+char(10),char(10))
		set @sql = replace(@sql,char(13),char(10))
		set @sql = replace(@sql,char(10),char(13)+char(10))
		set @idx = charindex(char(13),@sql,@idx)
		while @idx != 0 begin
			if @test is not null print @idx
			set @line = @line + 1
			set @sql = left(@sql,@idx-1) + '			--ln:' +  comdiv.tostring(@line) + ' ' +  right(@sql, len(@sql) - @idx + 1) 
			if @test is not null print @sql
			set @idx = charindex(char(13),@sql,charindex(char(13),@sql,@idx+1)+1)
		end
		set @sql = @sql + '			--ln:'+comdiv.tostring(@line+1)
		if @test is null update #preprocess set s = @sql
		if @test is not null print '--------------'
		if @test is not null print @sql
	end
end
go

/*@
<name>comdivpreprocessor.x010_foreach_operator_support</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="processforeachblocks"/>
<nolinenumber/>
<handler/>
<description>
SP foreach syntax to substitute native cursour hard syntax
activated always

supported syntax :

	declare @var1...,
	--foreach:
		select @var1=...,....
	--begin:
		some operations
	--end;
will be expanded into totally formed 
cursor with fetching and allocate/deallocate 
in normal and catch block
NOTES:
1) multiple foreach in single proc SUPPORTED
2) NESTED FOREACH NOT SUPPORTED
3) while in cursors, select CANNOT have @var=column constructions,
in --foreach: block SELECT MUST express variable mapping such way

example of usage:

declare @x int,@y nvarchar(10)
	--foreach: 
		select @x=object_id, @y = type from sys.objects
	--begin:
		print @x
		print @y
	--end;
will be automatically expanded into:

declare @x int,@y nvarchar(10)
	
			begin try
				declare __x cursor for
		 
		select object_id,  type from sys.objects
	
				open __x
				begin
					fetch next from __x into @x,@y 
					while @@fetch_status = 0 begin
		
		print @x
		print @y
	
						fetch next from __x into @x,@y 	
					end
				end
				close __x
				deallocate __x
			end try
			begin catch
				close __x
				deallocate __x
				exec comdiv.throw '',18,'error in cursor'
			end catch
NOTE:
if @txt parameter provided, procedure don't work as preprocessor, but
simply parses @txt and return result in @result parameter
</description>
@*/
create proc _ @txt nvarchar(max) = null, @result nvarchar(max) = null output  as begin
	declare @autonome bit set @autonome = 0
	if isnull(@txt,'')!='' set @autonome = 1
	
	declare @s nvarchar(max) 

	if @autonome = 1 set @s = @txt
	else set @s = (select top 1 s from #preprocess)

	declare @foreach_idx int set @foreach_idx = 0
	declare @foreach_end_idx int set @foreach_end_idx = 0
	declare @foreach_counter int set @foreach_counter = 0
	declare @before_foreach nvarchar(max)
	declare @after_foreach nvarchar(max)
	declare @foreach nvarchar(max)
	declare @columns_block nvarchar(max)
	declare @source_columns_block nvarchar(max)
	declare @fetch_into_block nvarchar(max) set @fetch_into_block = ''
	declare @idx int
	declare @idx2 int
	declare @str nvarchar(max)

	-- main iteration through 
	set @foreach_idx = charindex('--foreach:', @s, @foreach_idx)	
	while @foreach_idx != 0 begin
		-- extract foreach operator itself
		set @fetch_into_block = ''
		set @foreach_counter = @foreach_counter + 1
		set @foreach_end_idx = charindex('--end;', @s , @foreach_idx)
		set @foreach = substring(@s,@foreach_idx,@foreach_end_idx + len('-end;') - @foreach_idx + 1)
		set @before_foreach = left(@s, @foreach_idx-1)
		set @after_foreach = right(@s, len(@s) - @foreach_end_idx - len ('--end;') +1  )
		
		--extract variables and clear columns in select clause
		set @idx = charindex('select ',@foreach)
		set @idx2 = charindex(' from', @foreach)
		set @columns_block = substring(@foreach , @idx+len('select ') ,  @idx2 - len('select ') - @idx)
		set @source_columns_block = @columns_block
		set @idx = 0
		-- now idx - start of variable, idx2 - end of variable
		set @idx = charindex('@',@source_columns_block,@idx)
		while @idx != 0 begin
			set @idx2 = charindex('=',@source_columns_block, @idx)
			if @fetch_into_block != ''  set @fetch_into_block = @fetch_into_block + ','
			set @str = substring(@source_columns_block, @idx, @idx2 - @idx)
			set @fetch_into_block = @fetch_into_block + @str
			set @idx = charindex('@', @source_columns_block, @idx+1)
			set @columns_block = replace(@columns_block, @str + '=', '')
		end
		set @foreach = replace(@foreach,@source_columns_block, @columns_block)
		-- so now we have - well formed SELECT and well formed list OF VARIABLES TO FETCH

		-- now we can totally express foreach logic:
		set @foreach = replace(@foreach,'--foreach:','
			begin try
				declare __x cursor for
		')
		set @foreach = replace(@foreach,'--begin:','
				open __x
				begin
					fetch next from __x into '+@fetch_into_block+'
					while @@fetch_status = 0 begin
		')
		set @foreach = replace(@foreach,'--end;','
						fetch next from __x into '+@fetch_into_block+'	
					end
				end
				close __x
				deallocate __x
			end try
			begin catch
				close __x
				deallocate __x
				exec comdiv.throw '''',18,''error in cursor''
			end catch
		')
		set @s = @before_foreach + @foreach + @after_foreach
		set @foreach_idx = charindex('--foreach:', @s, @foreach_idx + 1)
	end
	if @autonome = 1 set @result = @s
	else update #preprocess set s = @s
end
go

exec comdiv.defineschema 'comdiv_preprocess_replacing' -- extensions for replacer
go
exec comdiv.definetable 'comdiv_preprocess_replacing.lib'
go
exec comdiv.definecolumn 'comdiv_preprocess_replacing.lib','lib','nvarchar(255)'
exec comdiv.definecolumn 'comdiv_preprocess_replacing.lib','idx','int'
exec comdiv.definecolumn 'comdiv_preprocess_replacing.lib','find','nvarchar(400)'
exec comdiv.definecolumn 'comdiv_preprocess_replacing.lib','replace','nvarchar(400)'
go
exec comdiv.recreateindex  'comdiv_preprocess_replacing.lib','lib_idx','lib,idx'
go
/*@
<name>comdivpreprocessor.x920_apply_replacers</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="applyreplaces"/>
<nolinenumbers/>
<handler/>
<description>
define string substitutions in declaration block in form:
	&lt;replace from='string-to-find' to='string-to-replace' /&gt;
	(note that &lt; is here just for XML isues)
	all occurances of "from" will be replaced to "to"
REMEMBER that ALL PREPROCESSORS (BY MSSQL DESIGN) ARE APPLYED AFTER 
SOURCE SP IS COMPILED, SO WITHOUT REPLACING CODE STILL MUST BE CORRECT!!!

ORDER OF REPLACERS IS SIGNIFICANT AND WILL BE EQUAL TO ORDER IN XML
</description>
@*/ 
create proc _  @test nvarchar(max) = null as begin
	declare @sql nvarchar(max) set @sql =  (select top 1 s from #preprocess)
	declare @comments xml set @comments = comdiv.getdeclaration(@sql)
	create table #repl (f nvarchar(255), t nvarchar(255))
	declare @f nvarchar(255), @t nvarchar(255)
	declare @idx int
	set @idx = charindex('@*/',@sql,0)
	declare @decl nvarchar(max)
	declare @body nvarchar(max)
	set @decl = left(@sql,@idx)
	set @body = right(@sql,len(@sql)-@idx)

	
	insert #repl (f,t) select l.find,l.[REPLACE] from
		comdiv_preprocess_replacing.lib l
		join  
		@comments.nodes('//replacelib') as T(i) 
		on T.i.value('@id','nvarchar(255)') = l.lib
		order by l.idx
	insert #repl (f,t) select T.i.value('@from','nvarchar(255)'),T.i.value('@to','nvarchar(255)') from @comments.nodes('//replace') as T(i)
	if (select count(*) from #repl) != 0 begin
		--foreach:
			select @f = f, @t = t from #repl
		--begin:
			set @body = replace(@body,@f,@t)
		--end;
		set @sql = @decl + @body
		update #preprocess set s = @sql
	end
	
end
go



/*@
<name>comdiv.definetable</name>
<module name="comdiv.mtsql.core" class="ddl" method="definetable"/>
<description>
checks existense of table with given name, if 
table or view exists returns,
if not - creates table on given filegroup (or default)
with single 'ID bigint identity(10,10) primary key' field (10 range step - to simplify merging with manual data
</description>
@*/
create proc _ @name nvarchar(255),  @filegroup nvarchar(255) = 'PRIMARY', @useident bit = 1 as begin
	set @filegroup = isnull(@filegroup,'PRIMARY')
	declare @sname nvarchar(255) set @sname = replace(@name,'.','_')
	declare @ident nvarchar(255) set @ident = ''
	--declare @cs nvarchar(255) set @cs = ', cs xml column_set for all_sparse_columns'
	--if @@VERSION not like '%2008%' set @cs = ''
	if @useident = 1 set @ident =  'identity(10,10) constraint pk_<<@sname>> primary key'
	if object_id(@name) is null begin
		exec _ '<<<create table <<@name>> (id int <<@ident>>) on ''<<@filegroup>>'' >>>'
	end	
end
go



/*@
<name>comdiv.definecolumn</name>
<module name="comdiv.mtsql.core" class="ddl" method="definecolumn"/>
<description>
safely adds column to table if it is not still exists (don't REDEFINE IT)
</description>
@*/
create procedure _ 
	@table_name nvarchar(255), 
	@column_name nvarchar(255), 
	@column_definition nvarchar(max) ,
	@alter bit = 0
as begin
	set nocount on 
	set @alter = isnull(@alter,0)
	set @column_definition = replace(@column_definition,'"','''')
	declare @table_id int set @table_id = (select object_id(@table_name))
	if left(@table_name,1)!='#' and not exists (select column_id from sys.columns where object_id = @table_id and name = @column_name) begin
			exec _ '<<<alter table <<@table_name>> add <<@column_name>> <<@column_definition>> >>>'
		
	end
	if left(@table_name,1)='#' and not exists (select column_id from tempdb.sys.columns where object_id = object_id('tempdb..'+@table_name) and name = @column_name) begin
		exec _ '<<<alter table <<@table_name>> add <<@column_name>> <<@column_definition>> >>>'
	end
	
end

go

/*@
<name>comdivpreprocessor.x300_table_interface_creator</name>
<module name="comdiv.mtsql.core" class="interfaces" method="create_interface_preprocessor"/>
<description>
<handler/>
Make 'table interfaces' which apply given schema to target tables
support for ierarchy
</description>
<nolinenumbers />
<nodynamic/>
<nosplices/>
<aspect/>
@*/
create proc _ as begin
	if '$name' like 'comdiv_table_interface.%' begin
		declare @x xml set @x = comdiv.getdeclaration('$sql')
		declare 
			@import nvarchar(255), 
			@importc bit,
			@fname nvarchar(255), 
			@ftype nvarchar(255), 
			@fconstraint nvarchar(max),
			@method nvarchar(255),
			@methodbody nvarchar(max),
			@methodisconstraint bit,
			@str nvarchar(255)

		declare @call nvarchar(max) set @call = '
			declare @current_column nvarchar(255)
			declare @current_contraints bit
		'
		create table #import(s nvarchar(255),c bit)
		insert #import(s,c)
		select 
			T.i.value('.','nvarchar(255)') ,
			T.i.value('@cons','bit') 
			from @x.nodes('//import') as T(i)
		
		create table #methods(n nvarchar(255), b nvarchar(max),c bit)
		insert #methods(n,b,c)
		select 
			T.i.value('@name','nvarchar(255)') ,
			T.i.value('.','nvarchar(max)'),
			T.i.value('@isconstraint','bit')
			from @x.nodes('//method') as T(i)

		create table #fields(n nvarchar(255), t nvarchar(255), c nvarchar(max))
		insert #fields(n,t,c)
		select 
			T.f.value('@name','nvarchar(255)'), 
			T.f.value('@type','nvarchar(255)') ,
			T.f.value('@constraint','nvarchar(max)') 
			from @x.nodes('//field') as T(f)
		create table #keys(n nvarchar(255), t nvarchar(255), s nvarchar(255), nul bit, def nvarchar(255), c nvarchar(255), nn nvarchar(255))
		insert #keys (n,t,nul,def,s) 
		select 
			T.f.value('@name','nvarchar(255)'), 
			T.f.value('@table','nvarchar(255)') ,
			T.f.value('@null','bit') ,
			T.f.value('@default','nvarchar(255)') ,
			T.f.value('@schema','nvarchar(255)') 
			from @x.nodes('//key') as T(f)
		update #keys set
			 t = ISNULL(t, n),
			 def = isnull(' DEFAULT '+def, ''),
			  nn = case when ISNULL(nul,0)=0 then ' NULL ' else ' NOT NULL ' end,
			  s = ISNULL(s+'.','dbo.')
		insert #fields(n,t,c)
			select n, 'int', nn+' constraint fk_NSTABLE_'+n+' foreign key references '+s+t + '(id) '+def
			from #keys
		--foreach:
			select @import=s,@importc=c from #import
		--begin:
			set @str = cast(@importc as nvarchar(255))
			if @str is null set @str = 'null'
			set @call = @call + '
				set @current_contraints = '+@str+'
				set @current_contraints = isnull(@current_contraints, @useconstraints)
				exec comdiv_table_interface.'+@import+' @table = @table, @useconstraints = @useconstraints
			'
		--end;

		
		--foreach:
			select @fname = n, @ftype = t, @fconstraint = c from #fields
		--begin:	
			set @fconstraint = replace(@fconstraint,'NSTABLE','<<right(@table,len(@table)-charindex(''.'',@table))>>')	
			set @fconstraint = replace(@fconstraint,'THIS','<<@table>>')
			if @@VERSION not like '%2008%' begin
				set @fconstraint = replace(@fconstraint,'SPARSE','NULL')
			end
			set @call = @call + '
				set @current_column = '''+@ftype+'''
				if @useconstraints = 1 or ''SPARSE''='''+@fconstraint+''' begin
					if '''+@fconstraint+''' like '' as %'' set @current_column = ''''
					set @current_column = @current_column + '' '' + '''+@fconstraint+'''
				end 
				exec comdiv.definecolumn @table, '''+@fname+''', @current_column
			'
		--end;		

		--foreach:
			select @method=n , @methodbody=b,@methodisconstraint = c from #methods
		--begin:
			set @methodisconstraint  = ISNULL(@methodisconstraint,0)
			set @call = @call + '
				if @useconstraints=1 or 0='+ cast(@methodisconstraint as nvarchar(1))+' begin
				if left(@table,1)!=''#'' begin
				print ''create '+@method+'''
				declare  @'+@method+'_method nvarchar(255)
				set @'+@method+'_method = @table + ''_' + @method + '''
				exec comdiv._drop @'+@method+'_method -- free method before creation in body
			'
			if isnull(@methodbody,'')!='' begin
				set @methodbody = replace(@methodbody,'{{','<<')
				set @methodbody = replace(@methodbody,'}}','>>')
				set @methodbody = replace(@methodbody,'''','''''')
				set @methodbody = replace(@methodbody,' __ ',' <<@'+@method+'_method>> ')
				set @methodbody = replace(@methodbody,' THIS',' <<@table>>')
				set @methodbody = replace(@methodbody,'NSTHIS','<<replace(@table,''.'',''_'')>>') -- no schema this
				set @methodbody = replace(@methodbody,'NSTABLE','<<right(@table,len(@table)-charindex(''.'',@table))>>') -- no schema this
				set @methodbody = '
					/*@
					<name>{{@'+@method+'_method}}</name>
					<module name="sys.interface_implementations" class="_{{replace(@table,''.'',''_'')}}" method="'+@method+'" />
					<implements>'+replace('$name','comdiv_table_interface.','')+'</implements>
					@*/
				' + @methodbody
				set @call = @call + '
					exec _ ''<<<   
					-- provided by '+'$name' + ' interface
					
					'+	
						@methodbody +
					'  >>>''
				end
				end
				'
			end
		--end;
		insert #before(s) values (@call)
	end
end
go 
-- creates special DSL for defining replaces for inline interface definitions
delete comdiv_preprocess_replacing.lib where lib = 'interface_builder'
go
insert comdiv_preprocess_replacing.lib (lib,idx,find,replace)
	values 
	('interface_builder',010,'print ''','exec sp_executesql N'''),
	('interface_builder',020,'{','MTSQLSTART'),
	('interface_builder',030,'}','MTSQLENDPROC'),
	('interface_builder',040,'~',''''''''''''''''''),
	('interface_builder',050,'fld ','<field name="'),
	('interface_builder',060,'fkey ','<key schema="FKSCHEMA" null="0" default="-1" name="'),
	('interface_builder',070,'optkey ','<key schema="FKSCHEMA" null="1" name="'),
	('interface_builder',080,':::','" constraint="'),
	('interface_builder',090,'::','" type="'),
	('interface_builder',100,';','"/>'),
	('interface_builder',110,'str(','nvarchar('),
	('interface_builder',120,'#]','" method="_declare"/>'),
	('interface_builder',130,'[#','<name>comdiv_table_interface.'),
	('interface_builder',140,'#','</name><module name="INTERFACEPRODUCT.interfaces" class="'),
	('interface_builder',150,'[','<import>'),
	('interface_builder',160,']','</import>'),
	('interface_builder',170,'setdef','not null default')





/*@
<name>comdivpreprocessor.x300_table_interface_parameters_embeder</name>
<module name="comdiv.mtsql.core" class="interfaces" method="supply_non_optional_parameters_to_interfaces"/>
<description>
<handler/>
helper preprocessor that embeds @table and @useconstraints parameters 
if interface not include them
</description>
<nolinenumbers />

@*/
create proc _ as begin
	declare @sql nvarchar(max) set @sql = (select top 1 s from #preprocess)
	declare @name nvarchar(255) set @name = comdiv.getattribute(@sql,'name')
	if @name like 'comdiv_table_interface.%' begin
		set @sql = replace(@sql,'''$table''', '@table')
		if not @sql like '%@useconstraints bit%' begin
			set @sql = replace (@sql,@name+' as begin', @name+' @table nvarchar(255), @useconstraints bit as begin')
		end
		update #preprocess set s = @sql
	end
end
go

/*@
<name>comdiv.implement</name>
<module name="comdiv.mtsql.core" class="interfaces" method="implement"/>
<description>applyes given interface to given table</description>
<_trace/>
@*/
create proc _ @table nvarchar(255), @interface nvarchar(255), @useconstraints bit = 1 as begin
	set @useconstraints = isnull(@useconstraints,1)
	exec _ '<<<exec comdiv_table_interface.<<@interface>> @table = ''<<@table>>'', @useconstraints = <<@useconstraints>> >>>'
end
go

/*@
<name>comdiv.definefilegroup</name>
<module name="comdiv.mtsql.core" class="ddl" method="definefilegroup" />
<description>safe procedure to create (if not exists) a filegroup</description>
@*/
create proc _ @filegroup nvarchar(255) as begin
	if filegroup_id(@filegroup) is null begin
		exec _ '<<<alter database <<db_name()>> add filegroup <<@filegroup>> >>>'
	end
end
go

/*@
<name>comdiv.definefile</name>
<module name="comdiv.mtsql.core" class="ddl" method="definefile" />
<description>safe procedure to create (if not exists) a file</description>
@*/
create proc _ @name nvarchar(255),@filegroup nvarchar(255) as begin
	if file_id(@name) is null begin
		declare @root nvarchar(255)
		set @root = (select top 1 physical_name from sys.database_files)
		set @root = reverse(@root) 
		set @root = reverse(right(@root, len(@root) - charindex('\',@root,0) + 1))
		exec _ '<<<
				ALTER DATABASE <<db_name()>> ADD FILE ( 
					NAME = N''<<@name>>'', 
					FILENAME = N''<<@root>><<@name>>.ndf'' , 
					SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP <<@filegroup>>
				>>>'
	end
end
go

/*@
<name>comdiv.recreateindex</name>
<module name="comdiv.mtsql.core" class="ddl" method="recreateindex" />
<description>
creates or recreates index
</description>
@*/
create procedure _ 
	@table_name nvarchar(255), 
	@index_name nvarchar(255), 
	@index_definition nvarchar(255),
	@unique bit = 1 as 
begin
	set nocount on 
	declare @q nvarchar(max)
	if exists (select object_id from sys.indexes where object_id = object_id(@table_name) and name = @index_name) begin
		
		set @q = 'drop index '+@index_name+' on '+@table_name+' '
		exec sp_executesql @q
		
	end
	declare @uq nvarchar(10) set @uq = ''
	if @unique = 1 set @uq = 'unique'
	
	set @q = 'create '+@uq+' index '+@index_name+' on '+@table_name+' ('+@index_definition+')'
	exec sp_executesql @q
end
GO
/*@
<name>comdiv.checkidents</name>
<module name="comdiv.mtsql.core" class="utils" method="checkidents" />
<description>
reseeds all identity fields in whole database
</description>
@*/
create proc _  @double bit = 1 as begin
	declare @call nvarchar(max) set @call = 'dbcc checkident (''",$t/@n,"'',reseed,0) '
	if @double = 1 set @call = @call + 'dbcc checkident (''",$t/@n,"'',reseed)'
	set @call = '"begin try '+@call +' end try begin catch end catch"'
	exec comdiv.batch
		'n',
		'select schema_name(schema_id)+"."+name as n from sys.objects where type="U"',
		 @call,
		null,
		1
end
GO


/*@
<name>comdiv.runhandlers</name>
<module name="comdiv.mtsql.core" class="events" method="fire" />
<description>calls for 'event handlers' from specified namespace</description>
@*/
create proc _ @schema nvarchar(255), @data xml = null as begin
	declare @procname nvarchar(255)
	--foreach:
	select @procname=name from sys.objects where schema_id = schema_id(@schema) and type='P' order by name
	--begin:
	if not exists(select name from sys.parameters where object_id=object_id(@schema+'.'+@procname ) and name='@data') begin -- for non-parameter call use short syntax
		exec _ '<<< begin try exec {{@schema}}.{{@procname}} end try begin catch exec comdiv.printerror(''{{@procname}}'') end catch >>>'
	end else begin -- for handlers which accepts data we provide '@data' parameter
		declare @q nvarchar(max)
		set @q = 'begin try exec {{@schema}}.{{@procname}} @data = @data end try begin catch exec comdiv.printerror(''{{@procname}}'') end catch'
		exec sp_executesql @q, @parameters = N'@data xml', @data = @data
	end
	--end;
end
go
/*@
<name>comdivprivate.triggerview</name>
<module name="comdiv.mtsql.core" class="reflection" method="triggerview" />
@*/
create view _ as	
	select id, schemaname, name, sql from(
		select t.object_id as id,schema_name(o.schema_id) as schemaname , t.name as name, m.definition as sql
		from sys.triggers t join sys.objects o on o.object_id = t.parent_id join
		sys.sql_modules m on m.object_id = t.object_id where parent_id !=0
		union select t2.object_id,'dbo',t2.name, m2.definition from sys.triggers t2
		join sys.sql_modules m2 on m2.object_id = t2.object_id) f
go
/*@
<name>comdivprivate.schemaviewbasis</name>
<module name="comdiv.mtsql.core" class="reflection" method="prepareschemaview" />
@*/
create view _ as 
select routine_schema as SchemaName,routine_name as Name,routine_type as Type,
comdiv.getsubattribute(routine_definition,'module','name') as Module,
comdiv.getsubattribute(routine_definition,'module','class') as Class,
comdiv.getsubattribute(routine_definition,'module','method') as Method,
comdiv.getattribute(routine_definition,'implements') as Implements,
(case routine_schema when 'comdiv_table_interface' then 1 else 0 end) as IsInterface,
(case routine_schema when 'comdivpreprocessor' then 1 else 0 end) as IsPreporcessor,
(case when routine_schema like '%private' then 1 else 0 end) as IsPrivate,
comdiv.getattribute(routine_definition,'event') as Event,
comdiv.hasattribute(routine_definition,'handler') as IsHandler,
comdiv.getattribute(routine_definition,'name') as XmlName,
comdiv.getattribute(routine_definition,'description') as Description,
routine_definition as Sql,
comdiv.getsourcesql (routine_definition) as SourceSql
from INFORMATION_SCHEMA.ROUTINES
union 
select TABLE_SCHEMA, TABLE_NAME, 'VIEW',
comdiv.getsubattribute(view_definition,'module','name'),
comdiv.getsubattribute(view_definition,'module','class'),
comdiv.getsubattribute(view_definition,'module','method'), 
comdiv.getattribute(view_definition,'implements'),
0,0,
(case when TABLE_SCHEMA like '%private' then 1 else 0 end),
cast(comdiv.getattribute(view_definition,'event') as nvarchar(255)),
comdiv.hasattribute(view_definition,'handler') ,
comdiv.getattribute(view_definition,'name'),
comdiv.getattribute(view_definition,'description'),
view_definition,
comdiv.getsourcesql(view_definition)
from INFORMATION_SCHEMA.VIEWS
union
select schemaname, name, 'TRIGGER',
comdiv.getsubattribute(sql,'module','name') as Module,
comdiv.getsubattribute(sql,'module','class') as Class,
comdiv.getsubattribute(sql,'module','method') as Method,
comdiv.getattribute(sql,'implements'),
(case schemaname when 'comdiv_table_interface' then 1 else 0 end) as IsInterface,
(case schemaname when 'comdivpreprocessor' then 1 else 0 end) as IsPreporcessor,
(case when schemaname like '%private' then 1 else 0 end) as IsPrivate,
comdiv.getattribute(sql,'event') as Event,
comdiv.hasattribute(sql,'handler') as IsHandler,
comdiv.getattribute(sql,'name') as XmlName,
comdiv.getattribute(sql,'description') as Description,
sql,
comdiv.getsourcesql(sql)
 from  comdivprivate.triggerview

go
/*@
<name>comdiv.schemaview</name>
<module name="comdiv.mtsql.core" class="reflection" method="schemaview" />
@*/
create view _ as
select  *,SchemaName+'.'+Name as FullName, comdiv.getdeclaration(Sql) as Xml, case cast( comdiv.getdeclaration(Sql)  as nvarchar(max)) when '<empty/>' then 0 else 1 end as HasXml from comdivprivate.schemaviewbasis
go



/*@
<name>comdivpreprocessor.x930_apply_class_names</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="applyclassnames"/>
<nolinenumbers/>
<handler/>
<description>
applyes class names from comdiv.schemaview
</description>
@*/ 
create proc _  @test nvarchar(max) = null as begin
		-- prevents class name checking if schema views are still not created
	if 
		( 
			object_id('comdivprivate.triggerview') is null or
		
			object_id('comdivprivate.schemaviewbasis')	is null or
			
			object_id('comdiv.schemaview') is null
		) return
	
	declare @sql nvarchar(max) set @sql =  (select top 1 s from #preprocess)
	if comdiv.hasattribute(@sql,'noclasses')=1 return
	create table #repl (f nvarchar(255), t nvarchar(255))
	declare @f nvarchar(255), @t nvarchar(255)
	declare @idx int
	set @idx = charindex('@*/',@sql,0)
	declare @decl nvarchar(max)
	declare @body nvarchar(max)
	set @decl = left(@sql,@idx)
	set @body = right(@sql,len(@sql)-@idx)
	insert #repl (f,t) select Class+'.'+Method, FullName from comdiv.schemaview where isnull(Class+Method,'')!=''
	if (select count(*) from #repl) != 0 begin
		--foreach:
			select @f = f, @t = t from #repl
		--begin:
			set @body = replace(@body,@f,@t)
		--end;
		set @sql = @decl + @body
		update #preprocess set s = @sql
	end
	
end
go


/*@
<name>comdivpreprocessor.x940_apply_self_accessors</name>
<module name="comdiv.mtsql.core" class="preprocessor" method="apply_attribute_accessors"/>
<nolinenumbers/>
<handler/>
<description>
applyes class names from comdiv.schemaview
</description>
@*/ 
create proc _  @test nvarchar(max) = null as begin
		-- prevents class name checking if schema views are still not created
	if 
		( 
			object_id('comdivprivate.triggerview') is null or
		
			object_id('comdivprivate.schemaviewbasis')	is null or
			
			object_id('comdiv.schemaview') is null
		) return
	
	declare @sql nvarchar(max) set @sql =  (select top 1 s from #preprocess)
	declare @name nvarchar(255) set @name = comdiv.getattribute(@sql,'name')

	if (charindex('.',@name,0)=0) set @name = 'dbo.'+@name

	if comdiv.hasattribute(@sql,'noattributes')=1 return

	if not @sql like '%SELF.%' return

	declare @q nvarchar(max)
	set @q  ='
		declare @__mysql nvarchar(max) set @__mysql = (select sql from comdiv.schemaview where fullname = ''{{@name}}'')
	'
	exec comdivprivate.preprocess_insert_before @sql output,@q

	set @sql = replace(@sql,'SELF.ATTR(','comdiv.getattribute(@__mysql,' )
	set @sql = replace(@sql,'SELF.SUBATTR(','comdiv.getsubattribute(@__mysql,' )
	set @sql = replace(@sql,'SELF.HASATTR(','comdiv.hasattribute(@__mysql,' )
	set @sql = replace(@sql,'SELF.QUERY(','comdiv.getdeclaration(@__mysql).query(' )
	set @sql = replace(@sql,'SELF.NAME',@name )
	update #preprocess set s = @sql
end
go


exec comdiv.defineschema 'comdiv_schema_validator'
go

/*@
<name>comdiv.validateschema</name>
<module name="comdiv.mtsql.core" class="reflection" method="validate" />
<event>comdiv_schema_validator</event>
<_sql/>
@*/
create proc _ as begin	
	set nocount on
	declare @proc nvarchar(max), @sql nvarchar(max), @type nvarchar(max)
	create table #t (type nvarchar(255), object nvarchar(255), severity int,errorcode int, error nvarchar(max))
	declare @xmlerror bit set @xmlerror = 0
	declare @x xml
	--foreach:
		select @proc = sch+'.'+name , @type=type, @sql = def 
				from (select ROUTINE_SCHEMA sch,ROUTINE_NAME name, ROUTINE_TYPE type, ROUTINE_DEFINITION def from
					 INFORMATION_SCHEMA.ROUTINES union select 
					TABLE_SCHEMA,TABLE_NAME, 'VIEW', view_definition 
				from INFORMATION_SCHEMA.VIEWS)t
	--begin:
		begin try
			set @x = comdiv.getdeclaration(@sql)
		end try
		begin catch
			insert #t values (@type,@proc,100, -1,'error in sql: '+comdiv.formaterror(''))
			set @xmlerror = 1
		end catch
	--end;

	

	if @xmlerror = 0 
	begin
		insert #t select Type, FullName, 90, 10, 'xmlname "{{upper(XmlName)}}" not mathch sys name "{{upper(FullName)}}"' 

		from comdiv.schemaview where XmlName != FullName and HasXml = 1

		insert #t select Type, FullName, 80, 20, 'ambigous method "{{Class}}.{{Method}}" name - duplicated' 
			from comdiv.schemaview s1 where isnull(Class+Method,'')!='' and
			exists (select top 1 name from comdiv.schemaview s2 where s2.fullname !=s1.fullname and s1.class=s2.class and s1.method=s2.method)

		insert #t select Type, FullName, 80, 30,  'no table "' +replace(right(class,len(class)-1),'_','.')+' " existed under method' 
			from comdiv.schemaview where isnull(implements,'')!='' and object_id(replace(right(class,len(class)-1),'_','.')) is null
		
		insert #t select Type, FullName, 40, 40, 'module is not defined' 
			from comdiv.schemaview where isnull(Module,'')='' and HasXml = 1

		insert #t select Type, FullName, 20, 50, 'object is placed in event-handlers schema but not marked as hendler (mistake possible)' 
			from comdiv.schemaview where IsHandler = 0 and SchemaName in (select Event from comdiv.schemaview)

		insert #t select Type, FullName, 5,60, 'best practic is to setup class and method' 
			from comdiv.schemaview where HasXml = 1 and (isnull(Class,'')='' and isnull(Method,'')='')

		exec comdiv.runhandlers 'comdiv_schema_validator'
	end 
	else begin
		insert #t values ('SYSTEM','',100,'cannot proceed following validation before xml errors are fixed')
	end

	if(select count(*) from #t)!=0	select * from #t
	
	else	
		print 'Schema validated and it''s status is OK'

	declare @res int
	set @res = (select max(severity) from #t)
	return @res

	
end
go
/* block of schema testing */
declare @valid int
exec @valid = comdiv.validateschema
if @valid != 100
	select * from comdiv.schemaview where module = 'comdiv.mtsql.core'
go
/* block of module cleanup */
/*
if object_id('tempdb..#names') is not null drop table #names
create table #names (name nvarchar(255))
insert #names (name ) select fullname from comdiv.schemaview
where module like 'comdiv.mtsql.core%' and fullname!='comdiv._drop'
declare  @script nvarchar(max)
exec comdiv.batch 'name','#names','"exec comdiv._drop ''",$t/@name,"''"',null,1
drop procedure comdiv._drop
*/
